/*
* 工位管理模块
*/
const express = require('express');
const router = express.Router();
// 引入数据库
const db = require("../../../config/db");
// 引入时间包
const moment = require("moment");
// 引入方法库
const { toCamelCase, toUnderlineCase, verifyToken} = require("../../../config/utils");
// 引入日志
const log = require("../../log");

// 获取所有工位列表
router.get("/list", async (req, res) => {
    console.log("===========获取工位列表接口============");
    // 获取参数
    let { pageNum, pageSize, workstationCode, workstationName, workshopId, processId } = req.query;
    // 拼接sql语句
    let sql = "select * from md_workstation";
    let sql_where = ` where 1=1`;
    if(workstationCode){
        sql_where += ` and workstation_code like '%${workstationCode}%'`;
    }
    if(workstationName){
        sql_where += ` and workstation_name like '%${workstationName}%'`;
    }
    if(workshopId){
        sql_where += ` and workshop_id like '%${workshopId}%'`;
    }
    if(processId){
        sql_where += ` and process_id like '%${processId}%'`;
    }
    if(workstationCode || workstationName || workshopId || processId){
        sql += sql_where;
    }
    
    // 分页
    sql += ` limit ${(pageNum-1)*pageSize}, ${pageSize}`;
    // 查询数据库
    db.query("查询工位列表", sql, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询工位列表失败", data: err});
            return;
        }
        // 查询总数
        db.query("查询工位总数", `select count(*) as count from md_workstation ${sql_where}`, (err, count)=>{
            if(err){
                res.send({code: 500, msg: "查询工位总数失败", data: err});
                return;
            }
            if(result.length > 0){
                 // 变量Json数组key转成下划线第一个大写
                 result = result.map(item => {
                    return toCamelCase(item)
                })
                res.send({code: 200, msg: "查询工位列表成功", rows: result, total: count[0].count});
            }else{
                res.send({code: 200, msg: "查询工位列表成功", rows: [], total: 0});
            }
        })
    })
})

// 添加工位
router.post("/", async (req, res) => {
    console.log("===========添加工位接口============");
    // 获取参数
    let { workstationCode, workstationName, workstationAddress, enableFlag, remark, workshopId, processId } = req.body;
     // 获取创建人
     let {username} = verifyToken(req.headers.authorization);
     // 获取创建时间
     let createTime = moment().format("YYYY-MM-DD HH:mm:ss");
     // 判断工位编码和工位名称是否重复
     db.query("查询工位编码是否重复", `select * from md_workstation where workstation_code='${workstationCode}' or workstation_name = '${workstationName}'`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询工位编码是否重复失败", data: err});
            return;
        }
        if(result.length > 0){
            res.send({code: 500, msg: "工位编码或工位名称已存在", data: []});
            return;
        }
        // 根据车间id，工序id 聚合查询 查询车间名称，工序名称
        let sql_union = `select workshop_name from md_workshop where workshop_id = '${workshopId}' 
        union all 
        select process_name from pro_process where process_id = '${processId}'`
        db.query("查询车间名称，工序名称", sql_union, (err, sql_union_result)=>{
            if(err){
                res.send({code: 500, msg: "查询车间名称，工序名称失败", data: err});
                return;
            }
            if(sql_union_result.length > 0){
                // 车间名称
                let workshopName = sql_union_result[0].workshop_name;
                // 工序名称
                let processName = sql_union_result[1].workshop_name;
                // 插入数据库
                let sql = `insert into md_workstation (workstation_code, workstation_name, workshop_id, process_id, workstation_address, enable_flag, 
                remark, create_by, create_time, workshop_name, process_name, warehouse_id, warehouse_name, location_id, location_name, area_id, area_name) 
                values
                ('${workstationCode}', '${workstationName}', '${workshopId}', '${processId}', ${workstationAddress ? "'"+workstationAddress+"'" : null}, 
                '${enableFlag}',  ${remark ? "'"+remark+"'" : null}, '${username}', '${createTime}', '${workshopName}', '${processName}',
                '300', '线边库-虚拟', '100', '库位-虚拟', '200', '区域-虚拟')`;
                db.query("添加工位", sql, (err, result)=>{
                    if(err){
                        res.send({code: 500, msg: "添加工位失败", data: err});
                        return;
                    }
                    if(result.affectedRows > 0){
                        res.send({code: 200, msg: "添加工位成功", data: []});
                    }else{
                        res.send({code: 500, msg: "添加工位失败", data: []});
                    }
                })
            }
        })
     })
})

// 查询单个工位
router.get(/^\/(\d+)$/, (req, res) => {
    console.log("===========查询单个工位接口，正则匹配============");
    // 获取参数
    let url_arr = req.url.split("/");
    const id = url_arr[url_arr.length - 1];
   
    db.query("查询单个工位", `select * from md_workstation where workstation_id = ${id}`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询单个工位失败", data: err});
            return;
        }
        if(result.length > 0){
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
                return toCamelCase(item)
            })
            res.send({code: 200, msg: "查询单个工位成功", data: result[0]});
        }else{
            res.send({code: 500, msg: "查询单个工位失败"});
        }
    })
})

// 修改工位
router.put("/", async (req, res) => {
    console.log("===========修改工位接口============");
    // 获取参数
    let { workstationCode, workstationName, workstationAddress, enableFlag, remark, workshopId, processId, workstationId } = req.body;
     // 获取更新人
     let {username} = verifyToken(req.headers.authorization);
     // 获取更新时间
     let updateTime = moment().format("YYYY-MM-DD HH:mm:ss");
     // 判断工位编码和工位名称是否重复
     db.query("查询工位编码是否重复", `select * from md_workstation where (workstation_code='${workstationCode}' or workstation_name = '${workstationName}') and workstation_id != '${workstationId}'`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询工位编码是否重复失败", data: err});
            return;
        }
        if(result.length > 0){
            res.send({code: 500, msg: "工位编码或工位名称已存在", data: []});
            return;
        }
        // 根据车间id，工序id 聚合查询 查询车间名称，工序名称
        let sql_union = `select workshop_name from md_workshop where workshop_id = '${workshopId}' 
        union all 
        select process_name from pro_process where process_id = '${processId}'`
        db.query("查询车间名称，工序名称", sql_union, (err, sql_union_result)=>{
            if(err){
                res.send({code: 500, msg: "查询车间名称，工序名称失败", data: err});
                return;
            }
            if(sql_union_result.length > 0){
                // 车间名称
                let workshopName = sql_union_result[0].workshop_name;
                // 工序名称
                let processName = sql_union_result[1].workshop_name;
                // 更新数据库
                let sql_update = `update md_workstation set workstation_code = '${workstationCode}', workstation_name = '${workstationName}', 
                workshop_id = '${workshopId}', process_id = '${processId}', workstation_address = ${workstationAddress ? "'"+workstationAddress+"'" : null}, 
                enable_flag = '${enableFlag}', remark = ${remark ? "'"+remark+"'" : null}, update_by='${username}', update_time='${updateTime}', 
                workshop_name = '${workshopName}', process_name = '${processName}'
                where workstation_id = ${workstationId}`;
                // 更新工位
                db.query("更新工位", sql_update, (err, result)=>{
                    if(err){
                        res.send({code: 500, msg: "更新工位失败", data: err});
                        return;
                    }
                    if(result.affectedRows > 0){
                        res.send({code: 200, msg: "更新工位成功", data: []});
                    }else{
                        res.send({code: 500, msg: "更新工位失败", data: []});
                    }
                })
            }
        })
     })
})

// 删除工位
router.delete("/:id", (req, res) => {
    console.log("===========删除工位接口============");
    // 获取参数
    let {id} = req.params;
     // 拼接sql,删除多条数据
     let sql = `delete from md_workstation where workstation_id in(${id})`;
    // 查询数据库
    db.query("删除工位", sql, (err, result) => {
        if (err) {
            res.send({code: 500, msg: "删除工位失败", data: err});
            return
        }
        if(result.affectedRows > 0){
            res.send({code: 200, msg: "删除工位成功"})
        }else{
            res.send({code: 500, msg: "删除工位失败"})
        }
    })
})


module.exports = router;
